"""
    本SQL用于dashboard页面
"""

version_status_sql = """
SELECT
	status, count(1) as count
FROM
	zt_project
WHERE
	type='sprint'
AND
	deleted='0'
GROUP BY
	`status`
"""


# n 天内新增的Bug数
def construct_n_day_sql_lastday_increase_open_testtask(nday=7):
    return """
SELECT 
    count(id) as count 
FROM 
    `zt_testtask` 
where 
    deleted='0' 
and 
    `begin` = DATE_SUB(CURRENT_DATE,INTERVAL %d DAY) 
and 
    `status`='doing';
    """ % nday



# n 天内新增关闭Bug数
def construct_n_day_sql_lastday_increase_done_tasttask(nday=7):
    return """
select 
     count(id) as count
from
    zt_testtask
where
    deleted="0"
and 
    `end`=DATE_SUB(CURRENT_DATE, INTERVAL %d DAY)
AND
    `status` = 'done';
    """ % nday


# 未解决的Bug数
unsolved_bug_sql = """
select 
    bug.`status`, count(1) as count
from 
    zt_bug as bug
LEFT JOIN
    zt_project as pj 
ON
    pj.id=bug.project
LEFT JOIN
    zt_project as pject 
ON
    pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
where 
    bug.deleted='0'
and 
        pject.status != 'closed'
group by 
    bug.`status`

"""


# 昨日新增未解决的Bug数
def construct_n_day_sql_lastday_increase_unsolved_bug(nday):
    return """
select 
    bug.`status`, count(1) as count
from 
    zt_bug as bug
LEFT JOIN
    zt_project as pj 
ON
    pj.id=bug.project
LEFT JOIN
    zt_project as pject 
ON
    pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
where 
    bug.deleted='0'
and 
    pject.status != 'closed'
and
    bug.resolution = ''
and 
    DATE(bug.`openedDate`) = DATE_SUB(CURRENT_DATE,INTERVAL %d DAY)
    """ % (nday)


# 每天未解决的Bug数
def construct_bug_each_day_unsolved_plot_sql(last_n_day=1):
    return """
select 
	count(id) as count
from 
	zt_bug
where 
	`status` = 'active'
and 
    `resolution` = ''
and
	`deleted` = '0'
and 
	date(`openedDate`) <= DATE_SUB(CURRENT_DATE,INTERVAL %d day);
    """ % last_n_day



# 按照项目统计查询所有迭代版本的状态统计
project_statistic_iteration_status_num_sql = """
SELECT
	proj.id,
	proj.name as name, 
	count(IF(pject.`status`='doing',true,null)) as doing,
	count(IF(pject.`status`='suspended',true,null)) as suspended,
	count(IF(pject.`status`='wait',true,null)) as wait,
	count(IF(pject.`status`='closed',true,null)) as closed
FROM
	zt_project as pject
LEFT JOIN
	zt_project as pj 
on 
	pj.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pject.path,',',2),',',-1)
LEFT JOIN
	zt_project as proj
on 
	proj.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
WHERE
	pject.type='sprint'
AND
	pject.deleted='0'
GROUP BY
	proj.id
"""



# 构造正常关闭的测试单的sql语句
def construct_testtask_sql_normal_closed_testtask(_date_range=7):
    return  """
SELECT 
        count(id) as count 
from 
        zt_testtask
where 
        deleted = '0'
and 
        DATE(`end`) > DATE(`realFinishedDate`)
and
        DATE(`end`) > DATE_SUB(CURRENT_DATE, INTERVAL %d day);
    """ % _date_range


# 构造延期关闭的测试单的sql语句
def construct_testtask_sql_delay_closed_testtask(_date_range=7):
    return """
SELECT 
        count(id) as count 
from 
        zt_testtask
where 
        deleted = '0'
and 
        DATE(`end`) <= DATE(`realFinishedDate`)
and
        DATE(`end`) > DATE_SUB(CURRENT_DATE, INTERVAL %d day);
    """ % _date_range

# 定位项目
postion_project_id_names_sql = """
	SELECT
		id,
		(
			SELECT
				CASE pject.`name`
			WHEN "高精度定位平台" THEN
				"定位部"
			WHEN "中台技术" THEN
				"中台部"
			WHEN "平台算法部" THEN
				"平台算法"
			WHEN "卫惯组合终端算法" THEN
				"终端算法"
			END
		) AS NAME
	FROM
		zentao.zt_project AS pject
	WHERE
		pject. NAME IN (
			"高精度定位平台",
			"中台技术",
			"平台算法部",
			"卫惯组合终端算法"
		)
	and
	    deleted="0";
"""


# 地图项目
map_project_id_names_sql = """
SELECT
		id,
		(
			SELECT
				CASE pject.`name`
			WHEN "车路协同云控平台V1.0" THEN
			    "车路协同"
			WHEN "数字底座" THEN
			    "平台地图"
			WHEN "车端地图引擎v2.0" THEN
			    "车端地图"
			END
		)
		as name
FROM
		zentao.zt_project AS pject
WHERE
        pject.`deleted`='0'
and
		pject.name IN (
 				"车路协同云控平台V1.0",
 				"数字底座",
				"车端地图引擎v2.0"
		)
"""


# 交付项目
deliver_project_id_names_sql = """
	SELECT
		id, 
		( SELECT
				case pject.`name`
		WHEN "智能网联汽车大数据云控基础平台项目(136)" 
		THEN "136项目"
		WHEN "北京市北斗融合创新应用示范项目"
		THEN "北斗办"
		WHEN "奇瑞监控显示平台v1.1"
		THEN "奇瑞项目"
		END ) as name 
FROM
		zentao.zt_project AS pject
WHERE
		pject.name IN (
 				"智能网联汽车大数据云控基础平台项目(136)",
 				"北京市北斗融合创新应用示范项目",
				"奇瑞监控显示平台v1.1"
		)
"""


# Bug创建排行榜前6名
Bug_creator_rank_6 = """
SELECT 
		usr.realname as name,count(*) as count 	
FROM
		zt_bug as bug
LEFT JOIN
		zt_user as usr
ON
		usr.account=bug.openedBy
where
        bug.deleted='0'
GROUP BY
		bug.openedBy
ORDER BY
		count
desc
LIMIT 6
"""


# Bug未解决排行榜前6名
Bug_unsolved_rank_6 = """
SELECT 
		usr.realname as name,count(*) as count 	
FROM
		zt_bug as bug
LEFT JOIN
		zt_user as usr
ON
		usr.account=bug.assignedTo
WHERE
		bug.deleted='0'
AND
		bug.STATUS='resolved'
GROUP BY
		bug.assignedTo
ORDER BY
		count
desc
LIMIT 6	
"""


Bug_efficient_rank_6 = """
(SELECT 
		usr.realname as name,count(*) as count
FROM
		zt_bug as bug
LEFT JOIN
		zt_user as usr
ON
		usr.account=bug.openedBy
WHERE
		bug.deleted='0'
AND
		bug.resolution in ('postponed', 'fixed')
GROUP BY
		bug.openedBy
ORDER BY
		count
desc
LIMIT 30)
UNION ALL
(SELECT 
		usr.realname as name,count(*) as count
FROM
		zt_bug as bug
LEFT JOIN
		zt_user as usr
ON
		usr.account=bug.openedBy
WHERE
		bug.deleted='0'
GROUP BY
		bug.openedBy
ORDER BY
		count
desc
LIMIT 30)
"""


# 历史Bug优先级分布
history_bug_priority_discribute = """
select 
		severity,count(bug.id) as count
from
		zt_bug as bug
LEFT JOIN
		zt_project as pj 
ON
		pj.id=bug.project
LEFT JOIN 
		zt_project as pject 
ON
		pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
WHERE
			bug.deleted='0'
and 
        pject.status != 'closed'
GROUP BY
		bug.severity

"""


# 构造日期时长的历史未解决Bug超时的SQL
def construct_bug_sql_history_Bug_delay(delay_days_start=2, delay_days_end=7):
    if delay_days_start > delay_days_end:
        return ''
    return """
SELECT
        count(bug.id) as count 
from 
        zt_bug as bug
LEFT JOIN
		zt_project as pj 
ON
		pj.id=bug.project
LEFT JOIN 
		zt_project as pject 
ON
		pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
WHERE
        bug.deleted='0'
and 
        pject.status != 'closed'
AND
        bug.openedDate < DATE_SUB(CURRENT_DATE,INTERVAL %d DAY)
and 
        bug.openedDate >= DATE_SUB(CURRENT_DATE,INTERVAL %d DAY)
AND
        bug.resolution = ''
""" % (delay_days_start, delay_days_end)


# 每日新增 Bug 直方图
def construct_bug_each_day_add_histogram_sql(last_n_day=1):
    return """
select 
	count(id) as count
from 
	zt_bug
where 
	`deleted` = '0'
and 
	date(`openedDate`) <= DATE_SUB(CURRENT_DATE,INTERVAL %d day)
and 
    date(`openedDate`) > DATE_SUB(CURRENT_DATE,INTERVAL %d day);
    """ % (last_n_day, last_n_day + 1 )



# 每日新增 Bug 关闭 曲线图
def construct_bug_each_day_closed_line_sql(last_n_day=1):
    return """
select 
	count(id) as count
from 
	zt_bug
where 
	`deleted` = '0'
and 
	date(`closedDate`) <= DATE_SUB(CURRENT_DATE,INTERVAL %d day)
AND
	date(`closedDate`) > DATE_SUB(CURRENT_DATE,INTERVAL %d day);
    """ % (last_n_day, last_n_day + 1)



# 定位部未解決 Bug 统计
def construct_sql_position_bug_unsolved_statistics(project_ids):
    return f"""
select 
		count(bug.id) as count
from
		zt_bug as bug 
LEFT JOIN
		zt_project as pject 
ON
		bug.project=pject.id 
WHERE
		bug.deleted= '0' 
AND
        bug.resolution = ''
AND
		SUBSTRING_INDEX(SUBSTRING_INDEX(pject.path,',', 2), ',',-1) 
in 	
		{project_ids} 
;
"""


#地图部未解决 Bug 统计
def construct_sql_map_bug_unsolved_statistics(project_ids):
    return f"""
select 
		count(bug.id) as count
from
		zt_bug as bug 
LEFT JOIN
		zt_project as pject 
ON
		bug.project=pject.id 
WHERE
		bug.deleted= '0' 
AND
        bug.resolution = ''
AND
		SUBSTRING_INDEX(SUBSTRING_INDEX(pject.path,',', 2), ',',-1) 
in 	
			{project_ids}
		;
"""


#地图部未解决 Bug 统计
def construct_sql_deliver_bug_unsolved_statistics(project_ids):
    return f"""
select 
		count(bug.id) as count
from
		zt_bug as bug 
LEFT JOIN
		zt_project as pject 
ON
		bug.project=pject.id 
WHERE
		bug.deleted= '0' 
AND
        bug.resolution = ''
AND
		SUBSTRING_INDEX(SUBSTRING_INDEX(pject.path,',', 2), ',',-1)
in 	
		{project_ids}
		;
"""


# 3大板块测试单状态分类数量统计
def construct_sql_project_collection_testtask_staus_statistics(project_ids, date_range):
    return f"""
SELECT
		pjectB.`name` as name,
		count(IF(task.`status`='doing',True,null)) as doing,
		count(IF( task.`status`='done',True,null)) as closed,
		count(IF(task.`status`='blocked',True,null)) as blocked,
		count(IF(task.`status`='wait',True,null)) as wait
FROM
		zentao.zt_testtask as task 
LEFT JOIN
		zentao.zt_project as pject
ON
		task.project = pject.id
LEFT JOIN
	zentao.zt_project as pjectB
ON
	pjectB.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pject.path, ',', 2), ',', -1)
WHERE 

		task.deleted = '0'
AND
		SUBSTRING_INDEX(SUBSTRING_INDEX(pject.path, ',', 2), ',', -1)
IN
		{project_ids}
AND
    	date(task.begin) <= DATE_SUB(CURRENT_DATE,INTERVAL {date_range} day)
GROUP BY
		pjectB.`name`;
"""


Bug_detail_list_of_delay_30_and_severity_1_sql = """
select
		pject.id as project_id,
		pject.name as project_name,
		IF(pj.name is null, prod.name, pj.name) as subsys_name,
		bug.id as bug_id,
		bug.severity as severity,
		bug.title as title,
		usr.realname as creator,
		bug.openedDate as create_date,
		usrB.realname as assigner,
		DATEDIFF(now(),bug.openedDate) as delay_days,
		bug.status as status
FROM
		zt_bug as bug 
LEFT JOIN
		zt_project as pj 
ON
		pj.id=bug.project
LEFT JOIN
		zt_project as pject 
ON
		pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
LEFT JOIN 
        zt_product as prod 
on 
        prod.id = bug.product
LEFT JOIN
		zt_user as usr 
ON
		usr.account=bug.openedBy
LEFT JOIN
		zt_user as usrB 
ON
		usrB.account=bug.assignedTo
WHERE
		bug.deleted='0'
and
        pject.status != 'closed'
and
        bug.status ='active'
AND
		(bug.severity < 3
or
		DATEDIFF(now(),bug.openedDate) >= 30)
order by 
        delay_days
desc 
"""




























